{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b671ec7d-4eee-4bad-ae02-f98cf1a7f641",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>model_type</th>\n",
       "      <th>auc</th>\n",
       "      <th>ks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>F2</td>\n",
       "      <td>0.687744</td>\n",
       "      <td>0.332832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>C0</td>\n",
       "      <td>0.694744</td>\n",
       "      <td>0.269452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>I0</td>\n",
       "      <td>0.716031</td>\n",
       "      <td>0.279788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M0</td>\n",
       "      <td>0.744988</td>\n",
       "      <td>0.377310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>G2</td>\n",
       "      <td>0.754283</td>\n",
       "      <td>0.397871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A2</td>\n",
       "      <td>0.765102</td>\n",
       "      <td>0.517644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>D2</td>\n",
       "      <td>0.766050</td>\n",
       "      <td>0.488739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>L2</td>\n",
       "      <td>0.767606</td>\n",
       "      <td>0.406579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E0</td>\n",
       "      <td>0.772417</td>\n",
       "      <td>0.462030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>J0</td>\n",
       "      <td>0.791712</td>\n",
       "      <td>0.402314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>E1</td>\n",
       "      <td>0.801028</td>\n",
       "      <td>0.695706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B2</td>\n",
       "      <td>0.836771</td>\n",
       "      <td>0.544334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>C1</td>\n",
       "      <td>0.852095</td>\n",
       "      <td>0.569446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M1</td>\n",
       "      <td>0.860959</td>\n",
       "      <td>0.680908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>I1</td>\n",
       "      <td>0.861031</td>\n",
       "      <td>0.600382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>H2</td>\n",
       "      <td>0.890143</td>\n",
       "      <td>0.674584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>J1</td>\n",
       "      <td>0.891764</td>\n",
       "      <td>0.664922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>K1</td>\n",
       "      <td>0.965229</td>\n",
       "      <td>0.933099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>K0</td>\n",
       "      <td>0.981725</td>\n",
       "      <td>0.958932</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   model_type       auc        ks\n",
       "17         F2  0.687744  0.332832\n",
       "9          C0  0.694744  0.269452\n",
       "11         I0  0.716031  0.279788\n",
       "2          M0  0.744988  0.377310\n",
       "12         G2  0.754283  0.397871\n",
       "0          A2  0.765102  0.517644\n",
       "18         D2  0.766050  0.488739\n",
       "14         L2  0.767606  0.406579\n",
       "3          E0  0.772417  0.462030\n",
       "6          J0  0.791712  0.402314\n",
       "13         E1  0.801028  0.695706\n",
       "7          B2  0.836771  0.544334\n",
       "8          C1  0.852095  0.569446\n",
       "1          M1  0.860959  0.680908\n",
       "10         I1  0.861031  0.600382\n",
       "4          H2  0.890143  0.674584\n",
       "5          J1  0.891764  0.664922\n",
       "15         K1  0.965229  0.933099\n",
       "16         K0  0.981725  0.958932"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#ks计算\n",
    "lst = []\n",
    "for model_type in d.model_type.unique():\n",
    "    lst.append(ks_auc(model_type,d))\n",
    "dfa = pd.DataFrame(lst)\n",
    "r5 = dfa#.to_csv(path + '各个模型的ks值与auc值.csv')\n",
    "r5 = r5.sort_values('auc')\n",
    "r5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "32f233b9-8854-4c7b-af91-7af94fc37f47",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                  e_score    s_score    g_score  esg_score model_type\n",
      "type              float64    float64    float64    float64         D2\n",
      "size                  862        862        862        862         D2\n",
      "missing             0.00%      0.00%      0.00%      0.00%         D2\n",
      "unique                  5         47         84        155         D2\n",
      "mean_or_top1    20.985296  28.869671  32.068777  81.923743         D2\n",
      "...                   ...        ...        ...        ...        ...\n",
      "50%_or_bottom5       18.0       24.8       33.3       77.3         A2\n",
      "75%_or_bottom4       18.0  26.834375       34.5    78.9125         A2\n",
      "90%_or_bottom3       18.0    29.3575       34.8  81.454375         A2\n",
      "99%_or_bottom2       24.0  32.096188       36.0  86.304438         A2\n",
      "max_or_bottom1       24.0   32.70625       36.0       89.4         A2\n",
      "\n",
      "[266 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "#描述性统计\n",
    "import toad as td \n",
    "df.columns\n",
    "final = pd.DataFrame()\n",
    "for model_type in df.model_type.unique():\n",
    "    temp= df.loc[df.model_type == model_type,['e_score','s_score','g_score','esg_score']]\n",
    "    result = td.detect(temp).T\n",
    "    result['model_type'] = model_type\n",
    "    final = pd.concat([result,final])\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "eebdaea6-52a3-428e-8a41-8d85d1d662b7",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>model_type</th>\n",
       "      <th>esg_rating</th>\n",
       "      <th>label_count</th>\n",
       "      <th>label_all</th>\n",
       "      <th>bad_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A2</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>H2</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>J1</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>237</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>D2</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>111</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>K1</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>J0</td>\n",
       "      <td>E</td>\n",
       "      <td>12</td>\n",
       "      <td>64</td>\n",
       "      <td>0.1875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>E0</td>\n",
       "      <td>E</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>D2</td>\n",
       "      <td>E</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>0.1304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>F2</td>\n",
       "      <td>E</td>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>0.1613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>M1</td>\n",
       "      <td>E</td>\n",
       "      <td>8</td>\n",
       "      <td>68</td>\n",
       "      <td>0.1176</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   model_type esg_rating  label_count  label_all  bad_ratio\n",
       "0          A2          A            0        106     0.0000\n",
       "45         H2          A            0         37     0.0000\n",
       "65         J1          A            0        237     0.0000\n",
       "20         D2          A            0        111     0.0000\n",
       "75         K1          A            0         81     0.0000\n",
       "..        ...        ...          ...        ...        ...\n",
       "64         J0          E           12         64     0.1875\n",
       "29         E0          E            2         65     0.0308\n",
       "24         D2          E            3         23     0.1304\n",
       "39         F2          E            5         31     0.1613\n",
       "94         M1          E            8         68     0.1176\n",
       "\n",
       "[95 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#每个模型好坏样本数\n",
    "d = d.rename(columns={'esg_rating2':'esg_rating'})\n",
    "#批量计算每个esg等级的坏样本数量\n",
    "left = d.groupby(['model_type','esg_rating']).label.sum().reset_index().rename(columns={'label':'label_count'})\n",
    "#按照模型统计坏样本数量后归一化展示esg等级对坏样本的区分度\n",
    "right = d.groupby(['model_type','esg_rating']).label.count().reset_index().rename(columns={'label':'label_all'})\n",
    "mege = pd.merge(left,right,on=['model_type','esg_rating'],how='left')\n",
    "mege['bad_ratio'] = round(mege.label_count / mege.label_all,4)\n",
    "r3 = mege.sort_values(['model_type','esg_rating'])#.to_csv(path + '各个模型好坏样本比率.csv')\n",
    "# right\n",
    "r3  =  r3.sort_values('esg_rating')\n",
    "r3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1f0c4fbc-21a6-4267-8d1f-a4968000dd5c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "r5.to_csv(path + '/每个模型ks值.csv')\n",
    "r3.to_csv(path + '/每个模型好坏样本个数与比率.csv')\n",
    "final.to_csv(path + '/各个模型分位数.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c2965907-d301-4285-a5fb-4cfbe16c75d7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#psi 与等级迁移率\n",
    "\n",
    "path = r'/notebooks/ESG/华夏模型/华夏模型10-19/数据分析'\n",
    "os.chdir(path + '/模型结果2022')\n",
    "l = pd.read_csv('评级结果.csv').rename(columns={'esg_rating2':'esg_rating2022'})\n",
    "os.chdir(path + '/模型结果2023')\n",
    "r = pd.read_csv('评级结果.csv').rename(columns={'esg_rating2':'esg_rating2023'})\n",
    "\n",
    "f = pd.merge(l[['custname','esg_rating2022']],r[['custname','esg_rating2023','model_type']],how='left',on='custname')\n",
    "# f.groupby(['model_type','esg_rating2022','esg_rating2023']).custname.count().reset_index().to_csv(path + '/等级迁移矩阵.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "883246a5-612c-4116-ab0c-d9b6e0df870f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.02714561739219699"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.metrics.PSI(f.esg_rating2022,f.esg_rating2023)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "58d7dfa2-e2bd-4e7d-848b-b4e3016cc87d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "d = f.copy()\n",
    "lst = []\n",
    "for model_type in d.model_type.unique():\n",
    "    def psi_(model_type,d):\n",
    "        temp = d.loc[d.model_type == model_type]\n",
    "        psi = td.metrics.PSI(temp.esg_rating2022,temp.esg_rating2023)\n",
    "        dic = {}\n",
    "        dic['model_type'] = model_type\n",
    "        dic['psi值'] =  psi \n",
    "        dic['psi值是否小于0.1'] = psi <= 0.1\n",
    "        return dic \n",
    "    lst.append(psi_(model_type,d))\n",
    "\n",
    "pd.DataFrame(lst).to_csv(path + '/2022与2023两期分数的psi值计算(稳定性分析).csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "7acd78a7-426d-4b33-87fb-5099aa96da8f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/notebooks/ESG/华夏模型/华夏模型10-19/数据分析'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
